<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Error handling</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, Perl, DBI, error handling, tutorial, 
database, programming, development">
<meta name="description" content="In this chapter of the SQLite Perl tutorial, 
we deal with errors.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Error handling</h1>

<p>
In this chapter we will show, how we can handle errors. 
</p>

<table>
    <tr class="hdr"><th>Method name</th><th>Description</th></tr>
    <tr class="gray"><td>$h->err()</td><td>Returns the native database engine error code from the last driver method called.</td></tr>
    <tr><td>$h->errstr()</td><td>Returns the native database engine error message from the last DBI method called.</td></tr>
    <tr class="gray"><td>$h->state()</td><td>Returns a state code in the standard SQLSTATE five character format</td></tr>
</table>

<p>
The above three methods deal with error messages. 
</p>

<table>
    <tr class="hdr"><th>DBI dynamic attribute</th><th>Description</th></tr>
    <tr class="gray"><td>$DBI::err</td><td>Equivalent to $h->err()</td></tr>
    <tr><td>$DBI::errstr</td><td>Equivalent to $h->errstr()</td></tr>
    <tr class="gray"><td>$DBI::state</td><td>Equivalent to $h->state()</td></tr>
</table>

<p>
The second table gives a list of DBI dynamic attributes, which are related
to error handling. These attributes have a short lifespan. They should be
used immediately after the method that might cause an error. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Default error handling</h2>

<p>
By default, the errors are returned by Perl DBI methods. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:SQLite:dbname=test.db";
my $user = '';
my $password = '';

my $dbh = DBI->connect($dsn, $user, $password) 
    or die "Can't connect to database: $DBI::errstr";

my $sth = $dbh->prepare( 
    q{ SELECT Id, Name, Price FROM Cars }
    )
    or die "Can't prepare statement: $DBI::errstr";

my $rc = $sth->execute()
    or die "Can't execute statement: $DBI::errstr";

while (my($id, $name, $price) = $sth->fetchrow()) {
    print "$id $name $price\n";
}

# check for problems which may have terminated the fetch early
warn $DBI::errstr if $DBI::err;

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the first script we deal with the default behavior of returning
error codes. 
</p>

<pre class="explanation">
my $dbh = DBI->connect($dsn, $user, $password) 
    or die "Can't connect to database: $DBI::errstr";
</pre>

<p>
We call the <code>connect()</code> method to create a database
connection. If the attempt fails, the method returns <code>undef</code> and sets both
$DBI::err and $DBI::errstr attributes. The <code>die()</code> method
prints the error message in case of a failure and terminates the script.
</p>

<pre class="explanation">
my $sth = $dbh->prepare( 
    q{ SELECT Id, Name, Price FROM Cars }
    )
    or die "Can't prepare statement: $DBI::errstr";
</pre>

<p>
We call the <code>prepare()</code> statement. If the method fails, the
<code>die()</code> method prints an error message and terminates the script.
</p>

<pre class="explanation">
my $rc = $sth->execute()
    or die "Can't execute statement: $DBI::errstr";
</pre>

<p>
Again. We call the <code>execute()</code> method and check for errors. 
The method returns <code>undef</code>, if it fails. 
</p>

<pre class="explanation">
warn $DBI::errstr if $DBI::err;
</pre>

<p>
We check for problems which may have terminated the fetch method early.
</p>


<h2>Raising exceptions</h2>

<p>
Checking for errors each time we call a DBI method may be tedious. We
could easily forget to do so, if we have a larger script. The preferred
way of dealing with possible errors is to raise exceptions. To raise
exceptions, we set the RaiseError attribute to true. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:SQLite:dbname=test.db";
my $user = '';
my $password = '';
my %attr = ( RaiseError => 1 );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
    or die "Can't connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");   
$sth->execute();

while (my($id, $name, $price) = $sth->fetchrow()) {
    print "$id $name $price\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the connection attributes, we set the RaiseError attribute to 1. 
When an error occurs, the exceptions are raised rather than error codes
returned. 
</p>

<pre class="explanation">
my %attr = ( RaiseError => 1 );
</pre>

<p>
We set the RaiseError attribute to 1. 
</p>

<pre class="explanation">
my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
    or die "Can't connect to database: $DBI::errstr";
</pre>

<p>
The <code>connect()</code> method is the only method, that we
check for the return code. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");   
$sth->execute();
</pre>

<p>
The <code>prepare()</code> and <code>execute()</code> methods
do not check for the return error codes. If they fail, an exception
is thrown and the Perl DBI will call the <code>die()</code> method
and print the error message. 
</p>


<h2>Error subroutines</h2>

<p>
With the HandleError connection handle attribute, we can set a reference
to a subroutine, which is called when an error is detected. The subroutine 
is called with three parameters: the error message string that RaiseError 
and "PrintError" would use, the DBI handle being used, and the first value 
being returned by the method that failed (typically undef).
</p>

<p>
If the subroutine returns a false value then the RaiseError and/or PrintError 
attributes are checked and acted upon as normal.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:SQLite:dbname=test.db";
my $user = '';
my $password = '';
my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&amp;handle_error );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
    or die "Can't connect to database: $DBI::errstr";

$dbh->do("UPDATE Cars SET Price=52000 WHERE Id=1");
$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8");

$dbh->commit();

$dbh->disconnect();

sub handle_error {

    $dbh->rollback();

    my $error = shift;
    print "An error occured in the script\n";
    print "Message: $error\n";
    return 1;
}
</pre>

<p>
Our own subroutine will handle the error. 
</p>

<pre class="explanation">
my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&amp;handle_error );
</pre>

<p>
The HandleError attribute provides a reference to a handle_error() subroutine, 
that is called, when an error is detected. The AutoCommit is turned off, which means
that we work with transactions.
</p>

<pre class="explanation">
$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8");
</pre>

<p>
There is an error in the SQL statement. There is no Car table. 
</p>

<pre class="explanation">
sub handle_error {

    $dbh->rollback();

    my $error = shift;
    print "An error occured in the script\n";
    print "Message: $error\n";
    return 1;
}
</pre>

<p>
This is the handle_error() subroutine. We rollback the changes .
We print the error message. And return 1. If we returned 0 instead, 
additional error messages would appear. Returning 1 error messages 
associated with the RaiseError attribute are supressed.
</p>

<pre>
$ ./errsub.pl
An error occured in the script
Message: DBD::SQLite::db do failed: no such table: Car
</pre>

<p>
Output of the example. 
</p>


<h2>The kosher example</h2>

<p>
According to the Perl DBI documentation, the most robust way to deal with 
DBI errors is to use the <code>eval()</code> method. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;
use DBI qw(:sql_types);

my $dsn = "dbi:SQLite:dbname=test.db";
my $user = '';
my $password = '';
my %attr = ( RaiseError => 1, AutoCommit => 0 );

my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
    or die "Can't connect to database: $DBI::errstr";

my @data = (
    [ 1, "Audi", 52642],
    [ 2, "Mercedes", 57127],
    [ 3, "Skoda", 9000], 
    [ 4, "Volvo", 29000], 
    [ 5, "Bentley", 350000], 
    [ 6, "Citroen", 21000],
    [ 7, "Hummer", 41400],
    [ 8, "Volkswagen", 21601] 
);

eval {
    $dbh->do("DROP TABLE IF EXISTS Cars");
    $dbh->do("CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INT)");
};

my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };
my $sth = $dbh->prepare( $sql );


foreach my $row (@data) {

  eval {
      $sth->bind_param( 1, @$row[0], SQL_INTEGER );
      $sth->bind_param( 2, @$row[1], SQL_VARCHAR );
      $sth->bind_param( 3, @$row[2], SQL_INTEGER );
      $sth->execute();
      $dbh->commit();
  };

  if( $@ ) {
      warn "Database error: $DBI::errstr\n";
      $dbh->rollback(); 
  }
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
The above code example is the most correct way of dealing with errors.
</p>

<pre class="explanation">
my %attr = ( RaiseError => 1, AutoCommit => 0 );
</pre>

<p>
We raise exceptions rather than check for return codes. We turn the autocommit
mode off and manually commit or rollback the changes. 
</p>

<pre class="explanation">
my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };
my $sth = $dbh->prepare( $sql );
</pre>

<p>
We guard against errors and security issues by using the placeholders.
</p>

<pre class="explanation">
eval {
    $sth->bind_param( 1, @$row[0], SQL_INTEGER );
    $sth->bind_param( 2, @$row[1], SQL_VARCHAR );
    $sth->bind_param( 3, @$row[2], SQL_INTEGER );
    $sth->execute();
    $dbh->commit();
};
</pre>

<p>
Inside the <code>eval()</code> method we put the error prone code. 
The method traps exceptions and fills the $@ special variable with
error messages. We bind the variables to the placeholders with the
<code>bind_param()</code> method.
</p>

<pre class="explanation">
if( $@ ) {
    warn "Database error: $DBI::errstr\n";
    $dbh->rollback(); 
}
</pre>

<p>
In case of an error, we print the error message and rollback the changes.
</p>

<p>
In this part of the SQLite Perl tutorial, we were discussing error handling.  
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 8, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

